package com.gxuwz.bignews.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "news_app.db";
    private static final int DATABASE_VERSION = 6;

    // 用户表
    public static final String TABLE_USERS = "users";
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_PHONE = "phone";
    public static final String COLUMN_PASSWORD = "password";
    public static final String COLUMN_USERNAME = "username";
    public static final String COLUMN_AVATAR = "avatar";//头像

    // 新闻表表名和字段常量
    public static final String TABLE_NEWS = "news"; // 新闻表名
    public static final String COLUMN_NEWS_UNIQUEKEY = "uniquekey"; // 新闻唯一ID
    public static final String COLUMN_NEWS_TITLE = "title"; // 新闻标题
    public static final String COLUMN_NEWS_DATE = "date"; // 新闻时间
    public static final String COLUMN_NEWS_CATEGORY = "category"; // 新闻分类
    public static final String COLUMN_NEWS_AUTHOR = "author_name"; // 新闻来源
    public static final String COLUMN_NEWS_URL = "url"; // 新闻访问链接
    public static final String COLUMN_NEWS_THUMBNAIL = "thumbnail_pic"; // 新闻图片链接
    public static final String COLUMN_NEWS_ISCONTENT = "is_content"; // 是否有内容详情

    // 历史表表名和字段常量
    /** 历史表表名 */
    public static final String TABLE_HISTORY = "news_history";
    /** 历史表自增主键ID */
    public static final String COLUMN_HISTORY_ID = "id";
    /** 历史表用户手机号 */
    public static final String COLUMN_HISTORY_PHONE = "phone";
    /** 历史表新闻唯一ID */
    public static final String COLUMN_HISTORY_UNIQUEKEY = "uniquekey";
    /** 历史表新闻标题 */
    public static final String COLUMN_HISTORY_TITLE = "title";
    /** 历史表新闻缩略图链接 */
    public static final String COLUMN_HISTORY_THUMBNAIL = "thumbnail_pic";
    /** 历史表新闻访问链接 */
    public static final String COLUMN_HISTORY_URL = "url";
    /** 历史表浏览时间（时间戳，毫秒） */
    public static final String COLUMN_HISTORY_TIME = "time";

    // 关注表表名和字段常量
    /** 关注表表名 */
    public static final String TABLE_FAVORITE = "news_favorite";
    /** 关注表自增主键ID */
    public static final String COLUMN_FAVORITE_ID = "id";
    /** 关注表用户手机号 */
    public static final String COLUMN_FAVORITE_PHONE = "phone";
    /** 关注表新闻唯一ID */
    public static final String COLUMN_FAVORITE_UNIQUEKEY = "uniquekey";
    /** 关注表新闻标题 */
    public static final String COLUMN_FAVORITE_TITLE = "title";
    /** 关注表新闻缩略图链接 */
    public static final String COLUMN_FAVORITE_THUMBNAIL = "thumbnail_pic";
    /** 关注表新闻访问链接 */
    public static final String COLUMN_FAVORITE_URL = "url";
    /** 关注时间（时间戳，毫秒） */
    public static final String COLUMN_FAVORITE_TIME = "time";

    // 创建用户表的SQL语句
    private static final String CREATE_TABLE_USERS = "CREATE TABLE " + TABLE_USERS + "("
            + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_PHONE + " TEXT UNIQUE NOT NULL,"
            + COLUMN_PASSWORD + " TEXT NOT NULL,"
            + COLUMN_USERNAME + " TEXT NOT NULL,"
            + COLUMN_AVATAR + " TEXT NOT NULL DEFAULT 'cat'"
            + ")";

    // 创建新闻表的SQL语句
    private static final String CREATE_TABLE_NEWS = "CREATE TABLE " + TABLE_NEWS + "(" +
            COLUMN_NEWS_UNIQUEKEY + " TEXT PRIMARY KEY," + // 新闻唯一ID
            COLUMN_NEWS_TITLE + " TEXT," +                // 新闻标题
            COLUMN_NEWS_DATE + " TEXT," +                 // 新闻时间
            COLUMN_NEWS_CATEGORY + " TEXT," +             // 新闻分类
            COLUMN_NEWS_AUTHOR + " TEXT," +               // 新闻来源
            COLUMN_NEWS_URL + " TEXT," +                  // 新闻访问链接
            COLUMN_NEWS_THUMBNAIL + " TEXT," +           // 新闻图片链接
            COLUMN_NEWS_ISCONTENT + " TEXT" +            // 是否有内容详情
            ")";

    // 创建历史表的SQL语句
    private static final String CREATE_TABLE_HISTORY = "CREATE TABLE " + TABLE_HISTORY + "(" +
            COLUMN_HISTORY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + // 自增主键ID
            COLUMN_HISTORY_PHONE + " TEXT NOT NULL," +                 // 用户手机号
            COLUMN_HISTORY_UNIQUEKEY + " TEXT NOT NULL," +             // 新闻唯一ID
            COLUMN_HISTORY_TITLE + " TEXT," +                          // 新闻标题
            COLUMN_HISTORY_THUMBNAIL + " TEXT," +                      // 新闻缩略图链接
            COLUMN_HISTORY_URL + " TEXT," +                            // 新闻访问链接
            COLUMN_HISTORY_TIME + " INTEGER," +                        // 浏览时间
            "UNIQUE(" + COLUMN_HISTORY_PHONE + "," + COLUMN_HISTORY_UNIQUEKEY + ") ON CONFLICT REPLACE" + // 保证同一用户同一新闻唯一，插入时自动更新
            ")";

    // 创建关注表的SQL语句
    private static final String CREATE_TABLE_FAVORITE = "CREATE TABLE " + TABLE_FAVORITE + "(" +
            COLUMN_FAVORITE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + // 自增主键ID
            COLUMN_FAVORITE_PHONE + " TEXT NOT NULL," +                 // 用户手机号
            COLUMN_FAVORITE_UNIQUEKEY + " TEXT NOT NULL," +             // 新闻唯一ID
            COLUMN_FAVORITE_TITLE + " TEXT," +                          // 新闻标题
            COLUMN_FAVORITE_THUMBNAIL + " TEXT," +                      // 新闻缩略图链接
            COLUMN_FAVORITE_URL + " TEXT," +                            // 新闻访问链接
            COLUMN_FAVORITE_TIME + " INTEGER," +                        // 关注时间
            "UNIQUE(" + COLUMN_FAVORITE_PHONE + "," + COLUMN_FAVORITE_UNIQUEKEY + ") ON CONFLICT REPLACE" + // 保证同一用户同一新闻唯一，插入时自动更新
            ")";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建用户表和新闻表
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_NEWS);
        // 创建历史表
        db.execSQL(CREATE_TABLE_HISTORY);
        // 创建关注表
        db.execSQL(CREATE_TABLE_FAVORITE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 数据库升级时，逐步补充新字段和新表
        if (oldVersion < 2) {
            db.execSQL("ALTER TABLE " + TABLE_USERS + " ADD COLUMN " + COLUMN_USERNAME + " TEXT NOT NULL DEFAULT ''");
        }
        if (oldVersion < 3) {
            db.execSQL("ALTER TABLE " + TABLE_USERS + " ADD COLUMN " + COLUMN_AVATAR + " TEXT NOT NULL DEFAULT 'cat'");
        }
        if (oldVersion < 4) {
            db.execSQL(CREATE_TABLE_NEWS); // 新增新闻表
        }
        if (oldVersion < 5) {
            db.execSQL(CREATE_TABLE_HISTORY); // 新增历史表
        }
        if (oldVersion < 6) {
            db.execSQL(CREATE_TABLE_FAVORITE); // 新增关注表
        }
    }
} 